package work.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import work.dto.Customer;
import work.util.DBUtility;

public class CustomerDAO {
	
	
	private static final CustomerDAO instance = new CustomerDAO();
	
	public static CustomerDAO getInstance() {
		return instance;
	}
	
	private CustomerDAO() {}
		
	
	public void join(Customer dto) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "insert into customers values(?, ?, ?, ?)";
		try {
			conn = DBUtility.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, dto.getUserid());
			pstmt.setString(2, dto.getUserpw());
			pstmt.setString(3, dto.getUsername());
			pstmt.setInt(4, dto.getMileage());
			
			pstmt.executeUpdate();
			
		} catch(SQLException e) {
			System.out.println("join error : " + e.getMessage());
		} finally {
			DBUtility.close(conn, pstmt);
		}
	}
	
	public boolean login(String userid, String userpw) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "select username from customers where userid=? and userpw=?";
		try {
			conn = DBUtility.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, userid);
			pstmt.setString(2, userpw);
			
			rs = pstmt.executeQuery();
			return rs.next();
			
		} catch(SQLException e) {
			System.out.println("login error : " + e.getMessage());
		} finally {
			DBUtility.close(conn, pstmt);
		}
		
		return false;
	}
	
	public ArrayList<Customer> searchAll() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from customers";
		
		ArrayList<Customer> list = new ArrayList<Customer>();
		
		try {
			conn =DBUtility.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				list.add(new Customer(rs.getString(1), rs.getString(2), rs.getString(3), rs.getInt(4)));
			}
			
		} catch(SQLException e) {
			System.out.println("searchAll error : " + e.getMessage());
		} finally {
			DBUtility.close(conn, pstmt);
		}
		
//		System.out.println("\n### searchAll : " + list);
		return list;		
	}
	
	public Customer searchDetail(String userid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from customers where userid=?";
		
		try {
			conn =DBUtility.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userid);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				return new Customer(rs.getString(1), rs.getString(2), rs.getString(3), rs.getInt(4));
			}
		} catch(SQLException e) {
				System.out.println("searchDetail error : " + e.getMessage());
		} finally {
				DBUtility.close(conn, pstmt);
			}
		return null;
	}
	
	public Customer update(String userid) {
		return searchDetail(userid);
	}
	
	public void updateSave(Customer dtoUpdate) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update customers set userpw=?, username=?, mileage=? where userid=?";
		
		try {
			conn =DBUtility.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, dtoUpdate.getUserpw());
			pstmt.setString(2, dtoUpdate.getUsername());
			pstmt.setInt(3, dtoUpdate.getMileage());
			pstmt.setString(4, dtoUpdate.getUserid());
			pstmt.executeUpdate();
			
		} catch(SQLException e) {
				System.out.println("searchSave error : " + e.getMessage());
				e.printStackTrace();
		} finally {
				DBUtility.close(conn, pstmt);
			}
		return;
	}

	public void delete(String userid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from customers where userid=?";
		
		try {
			conn =DBUtility.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, userid);
			pstmt.executeUpdate();
			
		} catch(SQLException e) {
				System.out.println("delete error : " + e.getMessage());
				e.printStackTrace();
		} finally {
				DBUtility.close(conn, pstmt);
			}
		return;
	}
	
	public ArrayList<Customer> multiSearch(String keywordType, String keyword) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql1 = "select * from customers where userid like ?";
		String sql2 = "select * from customers where username like ?";
		String sql3 = "select * from customers where mileage >= ?";
		
		ArrayList<Customer> list  = new ArrayList<Customer>();
		
		try {
			conn =DBUtility.getConnection();
			
			if (keywordType.equalsIgnoreCase("userid")) {
				pstmt = conn.prepareStatement(sql1);
				pstmt.setString(1, "%"+keyword+"%");
				
			}else if(keywordType.equalsIgnoreCase("username")) {
				pstmt = conn.prepareStatement(sql2);
				pstmt.setString(1, "%"+keyword+"%");
			} else if (keywordType.equalsIgnoreCase("mileage")) {
				pstmt = conn.prepareStatement(sql3);
				pstmt.setInt(1, Integer.parseInt(keyword));
			}
			
			rs = pstmt.executeQuery();
			while(rs.next()) {
				list.add(new Customer(rs.getString(1), rs.getString(2), rs.getString(3), rs.getInt(4)));
			}
			
		} catch(SQLException e) {
				System.out.println("delete error : " + e.getMessage());
				e.printStackTrace();
		} finally {
				DBUtility.close(conn, pstmt);
			}
		return list;
	}
}
